package com.testingedu.jdbcDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import com.testingedu.common.QueryResult;
import com.testingedu.db.DBManager;
import com.testingedu.model.TUser;
import com.testingedu.utils.Md5;

public class TUserDao extends BaseDao<TUser> {

	public TUserDao() {
		clazz = TUser.class;
	}

	public TUser login(TUser tUser) throws Exception {
		String sql = "select * from t_user where account = ?";
		List<Object> values = new ArrayList<Object>();
		values.add(tUser.getAccount());
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql, values);
			if (!rs.next()) {
				return null;
			}
			if(Md5.validPassword(tUser.getPassword(), rs.getString("password"))) {
				TUser sessionUser = new TUser();
				sessionUser.setId(rs.getInt("id"));
				sessionUser.setName(rs.getString("name"));
				return sessionUser;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			this.close();
		}
		return null;
	}

	public void list(QueryResult queryResult, TUser tUser) throws Exception {
		StringBuffer sql = new StringBuffer("select * from t_user tp where 1=1");
		List<Object> values = new ArrayList<Object>();
		if (tUser != null) {
			if (tUser.getId() != null && tUser.getId() > 0) {
				sql.append(" and tp.id = ?");
				values.add(tUser.getId());
			}
			if (tUser.getName() != null && tUser.getName().trim().length() > 0) {
				sql.append(" and tp.name like ?");
				values.add("%" + tUser.getName() + "%");
			}
			if (tUser.getAccount() != null
					&& tUser.getAccount().trim().length() > 0) {
				sql.append(" and tp.account like ?");
				values.add("%" + tUser.getAccount() + "%");
			}
		}
		sql.append(" limit ?,?");
		List<Object> countValues = new ArrayList<Object>(values);
		values.add(queryResult.getStartRow());
		values.add(queryResult.getPageSize());
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.toString(), values);
			queryResult.setResultList(_resultSetToList(rs));
		} finally {
			close();
		}
		sql.replace(7, 8, "count(*)");
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.substring(0,
					sql.length() - 10), countValues);
			if (rs.next()) {
				queryResult.setAllCount(new Long(rs.getInt(1)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
	}

	public void list(QueryResult queryResult,String userName, String roleName) throws Exception {
		StringBuffer sql = new StringBuffer("select * from t_user tu where tu.id in (select triu.user_id from t_role_in_user triu where triu.role_id in (select tr.id from t_role tr where tr.name = ?))");
		List<Object> values = new ArrayList<Object>();
		values.add(roleName);
		if(userName != null && userName.trim().length() > 0) {
			sql.append(" and tu.name like ?");
			values.add("%" + userName + "%");
		}
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.toString(), values);
			queryResult.setResultList(_resultSetToList(rs));
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			this.close();
		}
	}

	public void updatePass(String newPass, Integer id) throws Exception {
		String sql = "update t_user tu set tu.password = ? where tu.id = ?";
		List<Object> values = new ArrayList<Object>();
		values.add(Md5.getEncryptedPwd(newPass));
		values.add(id);
		try {
			this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql, values);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			this.close();
		}
	}
	
	public String resetPass(Integer id) throws Exception{
		String newPass = generateNewPass();
		updatePass(newPass, id);
		return newPass;
	}
	
	private static String generateNewPass() {
		StringBuffer sb = new StringBuffer();
		Random random = null;
		long seed = System.currentTimeMillis();
		while(sb.length() < 6) {
			random = new Random(seed);
			int num = random.nextInt(123);	//产生0到123之间的数
			if(num >= 48 && num <= 57) {
				sb.append((char)num);
			} else if(num >= 65 && num <= 90) {
				sb.append((char)num);
			} else if(num >= 97 && num <= 122) {
				sb.append((char)num);
			}
			seed += ++num;
		}
		return sb.toString();
	}

	public int add(TUser tUser)  {
		int id = 0;
		try {
			String sql = "insert into t_user(account,password,name,qq,description,certificate_id,certificate_name,phone,email,sex, show_info) values(?,?,?,?,?,?,?,?,?,?,?)";
			List<Object> values = new ArrayList<Object>();
			System.out.println("开始增加一个用户");
			values.add(tUser.getAccount());
			System.out.println("登录名：" + tUser.getAccount());
			values.add(Md5.getEncryptedPwd(tUser.getPassword()));
			System.out.println("密码：" + tUser.getPassword());
			values.add(tUser.getName());
			System.out.println("姓名：" + tUser.getName());
			values.add(tUser.getQq());
			values.add(tUser.getDescription());
			System.out.println("详细信息：" + tUser.getDescription());
			values.add(tUser.getCertificateId());
			values.add(tUser.getCertificateName());
			values.add(tUser.getPhone());
			System.out.println("电话号码：" + tUser.getPhone());
			values.add(tUser.getEmail());
			System.out.println("邮箱：" + tUser.getEmail());
			values.add(tUser.getSex());
			System.out.println("性别：" + tUser.getSex());
			if (tUser.getShowInfo() == null
					|| tUser.getShowInfo().trim().length() == 0) {
				tUser.setShowInfo("n");
			} else {
				tUser.setShowInfo("y");
			}
			values.add(tUser.getShowInfo());
			try {
				this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql, values);
			} catch (Exception e) {
				e.printStackTrace();
				throw new Exception();
			} finally {
				close();
			}
			sql = "select max(id) as _id from t_user";
			id = 0;
			try {
				this.myJdbcSetValuesAndExecuteQuery(sql, null);
				if (rs.next()) {
					id = rs.getInt("_id");
				}
			} catch (SQLException e) {
				e.printStackTrace();
				throw new Exception();
			} finally {
				this.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return id;
	}

	public void update(TUser tUser) throws Exception {
		String sql = "update t_user set name = ?, description = ?, phone = ?, email = ?, sex = ?, qq = ?, show_info = ? where id=?";
		List<Object> values = new ArrayList<Object>();
		values.add(tUser.getName());
		values.add(tUser.getDescription());
		values.add(tUser.getPhone());
		values.add(tUser.getEmail());
		values.add(tUser.getSex());
		values.add(tUser.getQq());
		if (tUser.getShowInfo() == null
				|| tUser.getShowInfo().trim().length() == 0) {
			tUser.setShowInfo("n");
		} else {
			tUser.setShowInfo("y");
		}
		values.add(tUser.getShowInfo());
		values.add(tUser.getId());
		try {
			this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql, values);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
	}

	public void delete(int id) throws Exception {
		String sql = "delete from t_user where id = ?";
		try {
			this.myJdbcSetValuesAndExecuteDelete(sql, id);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
	}

	public TUser info(int id) throws Exception {
		String sql = "select * from t_user tp where tp.id = ?";
		List<TUser> result = null;
		try {
			this.myJdbcSetValuesAndExecuteLoad(sql, id);
			result = _resultSetToList(rs);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
		if (result != null && result.size() > 0) {
			return result.get(0);
		}
		return null;
	}

	public static String[] getPhoneAndQq() {
		String sql = "select phone, qq from t_user tu where tu.show_info='y' and tu.id in (select triu.user_id from t_role_in_user triu where triu.role_id = 1) limit 1";
		Connection conn = DBManager.getConnection();
		String[] phoneAndQq = new String[2];
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()) {
				String phone = rs.getString("phone");
				if(phone != null && phone.trim().length() > 0)
					phoneAndQq[0] = phone;
				String qq = rs.getString("qq");
				if(qq != null && qq.trim().length() > 0)
					phoneAndQq[1] = qq;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ps != null)
					ps.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
			}
		}
		return phoneAndQq;
	}

	public static List<TUser> _resultSetToList(ResultSet rs) throws Exception {
		List<TUser> result = new ArrayList<TUser>();
		try {
			while (rs.next()) {
				TUser tUser = new TUser();
				tUser.setId(rs.getInt("id"));
				tUser.setAccount(rs.getString("account"));
				tUser.setSex(rs.getString("sex"));
				tUser.setEmail(rs.getString("email"));
				tUser.setShowInfo(rs.getString("show_info"));
				tUser.setQq(rs.getString("qq"));
				tUser.setPhone(rs.getString("phone"));
				tUser.setPassword(rs.getString("password"));
				tUser.setName(rs.getString("name"));
				tUser.setDescription(rs.getString("description"));
				result.add(tUser);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		}
		return result;
	}
}